• 
• 
• 

The Database Terms of Reference

Introduction

When we communicate about a particular subject area it is very important we use the common terms of reference. Relational database design is no different with its own set of unique terms, and in order to express or define our ideas and concepts we need to know the language to use. With Relational Databases this set of terms goes further being used to express and define the database design process itself and is used throughout the industry from publications and education to conversations between database professionals. In this section we are going to look at some of these terms and where they are used.

The process of creating a database can be broadly divided into two main stages:

1. Data analysis, using a formalised methodology to create a database design. Two widely used methods are Entity Relationship Modelling (ER) and Normalisation.
2. Physical implementation of that design in a database system. There are many examples of Relational databases including MySQL, Oracle, SQLServer to mention but a few.

As you move from a database design to a physical implementation, different terminology is used. It is important to understand these differences and ensure the correct terms are used for the appropriate Methodology or stage you are discussing or presenting.

The following table identifies each of the different disciplines and their equivalent terms in relation to the other disciplines.

It is a common misunderstanding that an Entity is like a Relation or that a Relation is a table. This is not true as they stem from very different disciplines within the Relational Database model and as such represent different descriptive types specific to that discipline. In saying this, the rational of each being representative of the other within the different disciplines can hold true. Therefore an Entity can be compared to a Relation in terms of design and used as validation. Likewise an Entity or Relation can become a table during the transition of implementation. This may seem somewhat pedantic but clarity of definition and scope will help to ensure you can correctly communicate your needs or requests to yourself and those around you. Poor use of terms can lead to confusion, misrepresentation or poor implementation.

Let us now examine each of the terms in the above table in more detail

Entity Relationship Modelling Terms

Entity:

A uniquely identifiable object of important from a top level perspective of an organisation or business model.

Entity Occurrence:

A single instance of an entity.

Attribute:

An identified element within an entity

If we consider a college as an example of something we might be modelling, a department, student or module might be examples of a uniquely identifiable object of important. It is worth noting that by convention we tend to name entities in the singular as in student rather than students.

Continuing with this example, within a Student entity we would have attributes such as studentId and studentName and an entity occurrence would be a single instance of these attributes.

Normalisation Terms

Relation:

A bottom up view of a design concept of a realisation of a potential database table taken from the movement of information within an organisation or business model

Tuple:

An ordered finite set of values of a relation.

Domain:

Defines the constraint and type of a single value element of a relation.

If we take a hotel as an example we might have Customer or Booking as examples of Relations with examples of Domains within a Booking being bookingRef, bookingDate and roomNo

Relational Database Terms

Table:

A table is the conceptual view of the database’s internal structure in the context of the 3 layer model.

Record/Row:

A row/record is a set of related data values of a common item.

Column:

A column is a data value of a particular item type.

This is the implementation of the design and an example can be seen in the following SQL statement

Models and Schemas

Relational Model

is representative of a single entity or relation within the context of a relational database where each of the elements of the entity or relation have been defined. For example a single entity has its attributes, constraints and keys defined which are representative of the completed table to be implemented.

Relational Schema

is a realisation of a relational model. It is the implementation of this model into the relational database. In other words, it is a physical single table within the database.

Relational Database Schema

is the collection of Relational Schemas and their relationships to each other as implemented into a relational database. In other words, it is a collection of physical tables and their relationships that make up the database as a whole.